package com.hspedu.jdbc.datasource;

import com.hspedu.jdbc.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 谷其昊
 * @version 1.0
 * @date 2022/4/11
 * @time 17:41
 */
public class JDBCUtilsByDruid_USE {

    public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id >= ?;";
        List<Actor> actorList =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        for (Actor actor : actorList) {
            System.out.println(actor);
        }
        JDBCUtilsByDruid.closeConnection(null,null,connection);
    }

    @Test
    public void testSelect() {
        System.out.println("使用 druid方式完成");
        //1. 得到连接
        Connection connection = null;
        //2. 组织一个sql
        String sql = "select * from actor where id >= ?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<Actor> actors = new ArrayList<>();
        //3. 创建PreparedStatement 对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            System.out.println(connection.getClass());
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            //执行, 得到结果集
            set = preparedStatement.executeQuery();

            //遍历该结果集
//            while (set.next()) {
//                int id = set.getInt("id");
//                String name = set.getString("name");
//                String sex = set.getString("sex");
//                LocalDateTime borndate = set.getLoca("borndate");
//                String phone = set.getString("phone");
//                actors.add(new Actor(id,name,sex,borndate,phone));
//                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            System.out.println("关闭资源");
            JDBCUtilsByDruid.closeConnection(set, preparedStatement, connection);
        }

        for (Actor actor : actors) {
            System.out.println(actor.getId()+ actor.getName()+actor.getSex());
        }
    }

    /**
     * 返回结果是多行的情况
     */
    @Test
    public void testQueryMany() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id >= ?;";
        List<Actor> actorList =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        for (Actor actor : actorList) {
            System.out.println(actor);
        }
        JDBCUtilsByDruid.closeConnection(null,null,connection);
    }

    /**
     * 查询的是单行多列的
     * @throws SQLException
     */
    @Test
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?;";
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 4);
        System.out.println(actor);
        JDBCUtilsByDruid.closeConnection(null,null,connection);
    }

    /**
     * 查询的是单行单列
     * @throws SQLException
     */
    @Test
    public void testScalar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id = ?;";
        Object o = queryRunner.query(connection, sql, new ScalarHandler(), 3);
        System.out.println(o);
        JDBCUtilsByDruid.closeConnection(null,null,connection);
    }

    @Test
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update actor set name = ? where id = ?;";
        int affectedRows = queryRunner.update(connection, sql, "张三丰", 3);
        System.out.println("affectedRows = " + affectedRows);
        JDBCUtilsByDruid.closeConnection(null,null, connection);
    }
}
